const { db, query } = require('../database/db')
// 添加宿舍记录
const add = (building_id, room_id, type, class_id, person_number, teacher, comment) => {
    let sql = `
        INSERT INTO hostels (building_id,room_id,type,class_id,person_number,teacher_id,comment)
            VALUES (?,?,?,?,?,?,?)
    `
    return query(sql, [building_id, room_id, type, class_id, person_number, teacher, comment])
}
// 删除宿舍记录-软删除
const del_f = (id) => {
    let sql = `
        UPDATE hostels SET state_del = 1 WHERE id = ?
    `
    return query(sql, [id])
}
const start = (id) => {
    let sql = `
        UPDATE hostels SET state_del = 0 WHERE id = ?
    `
    return query(sql, [id])
}

// 删除宿舍记录-真删除
const del_t = (id) => {
    let sql = `
        DELETE FROM hostels WHERE id = ?
    `
    return query(sql, [id])
}
// 宿舍的记录获取--get
const get = (id) => {
    let sql = `
        SELECT hostels.building_id,hostels.room_id,hostels.type,hostels.class_id,hostels.person_number,hostels.teacher,
            hostels.comment, 
            classes.name AS class_name,
            programs.name AS program_name,
            users.phone AS teacher_phone 
        FROM hostels 
        JOIN 
            classes ON hostels.class_id = classes.id 
        JOIN 
            programs ON classes.program_id = programs.id
        JOIN
            users ON hostels.teacher = users.truename
        WHERE hostels.id = ? AND hostels.state_del = 0
    `
    return query(sql, [id])
}
// 宿舍的记录更新数据--set
const set = (id, building_id, room_id, type, class_id, person_number, teacher, comment) => {
    let sql = `
        UPDATE hostels SET 
            building_id = ?, room_id = ?, type = ?, class_id = ?, person_number = ?, teacher_id = ?, comment = ?
        WHERE id = ?
    `
    return query(sql, [building_id, room_id, type, class_id, person_number, teacher, comment, id])
}
// 宿舍的记录的列表搜索分页
const list = async (room_id, building_id, class_id, type, program_id, page, page_size) => {
    // 构建基础SQL查询语句
    let sql_base = `
        SELECT hostels.id,hostels.building_id, hostels.room_id, hostels.type, 
            hostels.class_id, hostels.person_number, hostels.teacher_id,
            hostels.comment, hostels.state_del,
            classes.name AS class_name,
            programs.name AS program_name,
            programs.id AS program_id,
            users.truename as teacher_name,
            users.phone AS teacher_phone
        FROM hostels 
        JOIN classes ON hostels.class_id = classes.id 
        JOIN programs ON classes.program_id = programs.id 
        JOIN users ON hostels.teacher_id = users.id
    `;
    // 初始化参数数组
    let params = [];

    // 如果有搜索条件，添加到SQL语句中
    let conditions = [`0 = 0`]; // 初始化条件数组
    // 根据前端的请求数据加条件
    if (room_id) {
        conditions.push(`hostels.room_id = ?`);
        params.push(room_id);
    }
    if (building_id) {
        conditions.push(`hostels.building_id = ?`);
        params.push(building_id);
    }
    if (class_id) {
        conditions.push(`hostels.class_id = ?`);
        params.push(class_id);
    }
    if (type) {
        conditions.push(`hostels.type = ?`);
        params.push(type);
    }
    if (program_id) {
        conditions.push(`classes.program_id = ?`);
        params.push(program_id);
    }
    // 如果有搜索条件，添加WHERE子句
    if (conditions.length > 1) {
        sql_base += ` WHERE ${conditions.join(' AND ')}`;
    }

    // 添加分页的SQL语句
    sql_base += ` GROUP BY hostels.id DESC LIMIT ? OFFSET ?`;

    // 添加分页参数
    params.push(page_size);
    params.push((page - 1) * page_size);
    // 计算总数的SQL语句
    let countSql = `SELECT COUNT(*) as count FROM hostels WHERE ${conditions.join(' AND ')}`;
    // 执行查询
    const [assignmentsResult, countResult] = await Promise.all([
        query(sql_base, params), // 查询作业列表
        query(countSql, params) // 查询总数
    ]);
    return {
        assignments: assignmentsResult, // 作业列表
        total: countResult // 总数
    };
};
// 宿舍中的人员管理--添加宿舍人员
const person_add = async (hostel_id, student_id, bed_space) => {
    let sql = `
        INSERT INTO hostel_person (hostel_id, student_id, bed_space)
            VALUES (?,?,?)
    `
    let addSql = `
        UPDATE hostels 
        SET person_number = person_number + 1 
        WHERE hostels.id = ? 
        AND person_number < 4`  // 确保不超过上限
    // 执行查询
    const [Result, AddResult] = await Promise.all([
        query(sql, [hostel_id, student_id, bed_space]), // 查询作业列表
        query(addSql, [hostel_id]) // 查询总数
    ]);
    return Result
}
// 宿舍中的人员管理--删除宿舍记录-软删除
const person_del_f = async ( id,hostel_id) => {
    let sql = `
        UPDATE hostel_person SET state_del = 1 WHERE id = ?
    `
    let addSql = `
        UPDATE hostels 
        SET person_number = person_number - 1 
        WHERE hostels.id = ? `
    // 执行查询
    const [Result, ReduceResult] = await Promise.all([
        query(sql, [id]), // 查询作业列表
        query(addSql, [hostel_id]) // 查询总数
    ]);
    return {
        Result: Result,
        ReduceResult: ReduceResult 
    };
}

// 宿舍中的人员管理--删除宿舍记录-真删除
const person_del_t = (id) => {
    let sql = `
        DELETE FROM hostel_person WHERE id = ?
    `
    return query(sql, [id])
}
// 宿舍中的人员管理--宿舍的记录获取--get
const person_get = (id) => {
    let sql = `
        SELECT 
            hostel_person.bed_space, hostel_person.student_id,
            students.name AS student_name, students.student_id AS student_id, students.gender AS student_gender,
            students.phone AS student_phone, classes.name AS class_name  
        FROM hostel_person
        JOIN 
            students ON hostel_person.student_id = students.id 
        JOIN 
            classes ON students.class_id = classes.id 
        WHERE hostel_person.id = ? AND hostel_person.state_del = 0
    `
    return query(sql, [id])
}

// 宿舍中的人员管理--添加宿舍人员
const person_set = (id, hostel_id, student_id, bed_space) => {
    let sql = `
        UPDATE  hostel_person SET hostel_id=?, student_id=?, bed_space=?
        WHERE id = ?
    `
    return query(sql, [hostel_id, student_id, bed_space, id])
}

// 宿舍中的人员列表搜索
const person_list = async (hostel_id, page, page_size) => {
    let sql = `
        SELECT 
            hostel_person.bed_space,hostel_person.id,
            students.name AS student_name, students.student_id AS student_id, students.gender AS student_gender,
            students.phone AS student_phone, classes.name AS class_name ,students.id AS studentid
        FROM hostel_person
        JOIN 
            students ON hostel_person.student_id = students.id 
        JOIN 
            classes ON students.class_id = classes.id 
        WHERE hostel_person.state_del = 0 AND hostel_person.hostel_id = ? 
        LIMIT ? OFFSET ?
    `
    let countSql = `
        SELECT 
            COUNT(*) AS total 
        FROM hostel_person
        JOIN 
            students ON hostel_person.student_id = students.id 
        JOIN 
            classes ON students.class_id = classes.id 
        WHERE hostel_person.state_del = 0 AND hostel_person.hostel_id = ? 
        LIMIT ? OFFSET ?
    `
    const params = [hostel_id, page_size, (page - 1) * page_size]
    const [Result, countResult] = await Promise.all([
        query(sql, params), // 查询列表
        query(countSql, params) // 查询总数
    ]);
    return {
        list: Result, // 作业列表
        total: countResult // 总数
    };
}

// 根据传过来的班级id去查找对应的专业
const clsToprmM = (id) => {
    let sql = `
        SELECT 
            programs.name AS programs_name,programs.id AS programs_id
        FROM classes
        JOIN programs ON programs.id = classes.program_id
        WHERE classes.id = ?
    `
    return query(sql, [id])
}
module.exports = {
    add,
    del_f,
    del_t,
    get,
    set,
    list,
    person_add,
    person_del_f,
    person_del_t,
    person_get,
    person_set,
    person_list,
    clsToprmM,
    start
}